﻿DELETE FROM CARD_PERFORMANCE
DELETE FROM POS_PERFORMANCE

DECLARE @MONTH1 DATE=DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,(SELECT MAX_DATE_ENDMONTH FROM DATE_EM_REPORT))-10,0))
DECLARE @COUNT INT =1
DECLARE @FULLDATE NVARCHAR(8)
WHILE @COUNT <13
BEGIN
	SET @FULLDATE= CONVERT (NVARCHAR(8), @MONTH1 ,112)
	IF(@MONTH1 = '20160430') SET @FULLDATE = '20160429'
	DECLARE @STRCOUNT NVARCHAR(6) = CONVERT(NVARCHAR(6),@MONTH1,112)
	
PRINT @STRCOUNT
-----------H.1.THẺ 
PRINT 'CARD PERFORMANCE'

	EXEC('
INSERT CARD_PERFORMANCE
SELECT YEARMONTH, T24_DAO AS DAO, LOAI_THE, THE_KICH_HOAT,  COUNT(ACNT_CONTRACT_ID) AS NO_OF_CARD, SUM(KPI_CARD) AS KPI_CARD
FROM (
SELECT YEARMONTH, T24_DAO, ACNT_CONTRACT_ID,LOAI_THE ,THE_KICH_HOAT, CASE WHEN LOAI_THE=''DEBIT'' AND RN<3 THEN 1 
WHEN LOAI_THE=''CREDIT'' AND RN< 4 THEN 5 ELSE 0 END AS KPI_CARD
FROM 
(
SELECT YEARMONTH, T24_DAO, ACNT_CONTRACT_OID, ACNT_CONTRACT_ID, CASE WHEN PRODUCT_GROUP LIKE ''%DEBIT%'' THEN ''DEBIT'' ELSE ''CREDIT'' END AS LOAI_THE, 
CASE WHEN CONVERT(NVARCHAR(6),UNLOCK_DATE,112)='+@STRCOUNT+' THEN ''NEW_ACTIVATE''
ELSE ''ACTIVATED'' END AS THE_KICH_HOAT, ROW_NUMBER() OVER (PARTITION BY ACNT_CONTRACT_OID ORDER BY UNLOCK_DATE ASC) AS RN
FROM HUONGTT19.CARD.DBO.CARD_ALL_'+@STRCOUNT+'
WHERE T24_DAO IS NOT NULL
--AND UNLOCK_DATE >=''20160101''
AND UNLOCK_CARD=''CARD_ACTIVE'' ) X) Y
GROUP BY YEARMONTH, T24_DAO, LOAI_THE, THE_KICH_HOAT
')

DECLARE @STRCOUNT_POS NVARCHAR(8) = @STRCOUNT
IF(@FULLDATE = '20160131') SET @STRCOUNT_POS = '20160131'
IF(@FULLDATE = '20160229') SET @STRCOUNT_POS = '20160229'
IF(@FULLDATE = '20160429') SET @STRCOUNT_POS = '20160430'
IF(@FULLDATE = '20160930') SET @STRCOUNT_POS = '20160930'
IF(@FULLDATE = '20161031') SET @STRCOUNT_POS = '20161031'

-----------------H.2.POS
PRINT 'POS PERFORMANCE'
IF(@MONTH1 < '20160101')
BEGIN
	EXEC('
INSERT POS_PERFORMANCE
SELECT YEARMONTH, DAO, MAY_LAP, COUNT(DISTINCT [ID] ) AS NO_OF_POS 
FROM (
SELECT YEARMONTH='''+@STRCOUNT+''', B.DAO AS DAO, A.[ID], 
CASE WHEN CONVERT(NVARCHAR(6), [POS_OPERATION_DATE],112)='''+@STRCOUNT+''' THEN ''NEW'' ELSE ''EXISTED'' END AS MAY_LAP
FROM HUONGTT19.POS.DBO.POS_ALL_'+@STRCOUNT_POS+' A
LEFT JOIN TBL_CUSTOMER B
ON A.CIF=B.CIF COLLATE DATABASE_DEFAULT
WHERE 
[STATUS] = ''2'' AND
--[NGÀY LẮP MÁY] >=''20160101'' AND 
B.DAO IS NOT NULL) X
GROUP BY YEARMONTH, DAO, MAY_LAP
')
END
IF(@MONTH1 >= '20160101')
BEGIN
	EXEC('
INSERT POS_PERFORMANCE
SELECT YEARMONTH, DAO, MAY_LAP, COUNT(DISTINCT [MÃ] ) AS NO_OF_POS 
FROM (
SELECT YEARMONTH='''+@STRCOUNT+''', B.DAO AS DAO, A.[MÃ], 
CASE WHEN CONVERT(NVARCHAR(6), [NGÀY LẮP MÁY],112)='''+@STRCOUNT+''' THEN ''NEW'' ELSE ''EXISTED'' END AS MAY_LAP
FROM HUONGTT19.POS.DBO.POS_ALL_'+@STRCOUNT_POS+' A
LEFT JOIN TBL_CUSTOMER B
ON A.CIF=B.CIF COLLATE DATABASE_DEFAULT
WHERE 
[TRẠNG THÁI TRIỂN KHAI] = ''2'' AND
--[NGÀY LẮP MÁY] >=''20160101'' AND 
B.DAO IS NOT NULL) X
GROUP BY YEARMONTH, DAO, MAY_LAP
')
END
	SET @MONTH1 = DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@MONTH1)+2,0))
SET @COUNT = @COUNT + 1
END

----------------H.3.PAYROLL
DELETE FROM FIRST_TRANS_PAYROLL

INSERT FIRST_TRANS_PAYROLL
SELECT * 
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY DEBIT_CIF ORDER BY TRANS_DATE ASC) AS RN
 FROM HUONGTT19.PAYROLL.DBO.[PAYROLL_ALL_LUY_KE])X
 WHERE X.RN=1 AND YEARMONTH >= '201511'
 ORDER BY YEARMONTH

 DELETE FROM PAYROLL_NEW

 INSERT PAYROLL_NEW
 SELECT A.YEARMONTH, B.DAO AS DAO, COUNT(DISTINCT DEBIT_CIF) AS NO_NEW_PAYROLL
 FROM FIRST_TRANS_PAYROLL A
 LEFT JOIN TBL_CUSTOMER B
 ON A.DEBIT_CIF=B.CIF
 WHERE A.SEGMENT='SMES'
 GROUP BY A.YEARMONTH, B.DAO
 ORDER BY YEARMONTH

 DELETE FROM PAYROLL_PERFORMANCE_LUY_KE

INSERT PAYROLL_PERFORMANCE_LUY_KE
SELECT A.YEARMONTH, B.DAO AS DAO, COUNT(TRANS_CODE) AS NO_OF_TRANS, COUNT(DISTINCT DEBIT_CIF) AS NO_CIF, SUM(TRANS_AMOUNT) AS TRANS_AMOUNT, 0
FROM HUONGTT19.PAYROLL.[DBO].[PAYROLL_ALL_LUY_KE] A
LEFT JOIN TBL_CUSTOMER B
ON A.DEBIT_CIF=B.CIF
WHERE A.SEGMENT='SMES' AND A.YEARMONTH >='201511'
GROUP BY A.YEARMONTH, B.DAO
ORDER BY YEARMONTH

UPDATE A
SET A.NO_NEW_PAYROLL=B.NO_NEW_PAYROLL
FROM PAYROLL_PERFORMANCE_LUY_KE A, PAYROLL_NEW B
WHERE A.DAO=B.DAO
AND A.YEARMONTH=B.YEARMONTH

-----------------H.4.B2B 

DELETE FROM B2B_PERFROMANCE

INSERT B2B_PERFROMANCE
 SELECT A.YEARMONTH, B.DAO, COUNT(TRANS_CODE) AS NO_OF_TRANS, COUNT(DISTINCT CIF) AS COUNT_CIF, SUM(AMOUT_QD) AS TRANS_AMOUNT, 0
  FROM HUONGTT19.B2B.[DBO].[B2B_TRANSFER_LUY_KE] A
LEFT JOIN TBL_CUSTOMER B
ON A.CUST_CIF=B.CIF
WHERE DAO IS NOT NULL AND A.YEARMONTH >='201511'
GROUP BY A.YEARMONTH, B.DAO
ORDER BY YEARMONTH

UPDATE A
SET A.NO_NEW_USER=B.NO_B2B_USER
FROM B2B_PERFROMANCE A, 
(SELECT CONVERT(NVARCHAR(6), OPEN_DATE,112) AS YEARMONTH, DAO_CIF AS DAO, COUNT(DISTINCT CIF) AS NO_B2B_USER
 FROM HUONGTT19.B2B.[DBO].[B2B_USER_20161208]
 WHERE SEGMENT='SMES'
 AND OPEN_DATE <='20161130'
 GROUP BY CONVERT(NVARCHAR(6), OPEN_DATE,112) , DAO_CIF
) B
WHERE A.DAO=B.DAO
AND A.YEARMONTH=B.YEARMONTH


-------------CREATE DATA CASH MANAGEMENT PERFORMANCE



delete from CASH_MANAGEMENT_PERFORMANCE_RAW

insert CASH_MANAGEMENT_PERFORMANCE_RAW
SELECT YEARMONTH, DAO, CASE 
							WHEN THE_KICH_HOAT = 'NEW_ACTIVATE' THEN 2 
							WHEN THE_KICH_HOAT = 'ACTIVATED' THEN 1 END AS GROUP_TYPE,
	CASE WHEN LOAI_THE = 'DEBIT' THEN 'DEBIT CARD'
		WHEN LOAI_THE = 'CREDIT' THEN 'CREDIT CARD' END AS PRODUCT,
		NO_OF_CARD AS QUANTITY, 0 AS BALANCE
 FROM CARD_PERFORMANCE


INSERT CASH_MANAGEMENT_PERFORMANCE_RAW
SELECT YEARMONTH, DAO, CASE 
							WHEN MAY_LAP = 'NEW' THEN 2 
							WHEN MAY_LAP = 'EXISTED' THEN 1 END AS GROUP_TYPE,
	'POS' AS PRODUCT, NO_OF_POS AS QUANTITY, 0 AS BALANCE
 FROM POS_PERFORMANCE
 


INSERT CASH_MANAGEMENT_PERFORMANCE_RAW
SELECT YEARMONTH, DAO, 1 AS GROUP_TYPE,
	'PAYROLL' AS PRODUCT, NO_CIF AS QUANTITY, TRANS_AMOUNT AS BALANCE
 FROM PAYROLL_PERFORMANCE_LUY_KE

 
INSERT CASH_MANAGEMENT_PERFORMANCE_RAW
SELECT YEARMONTH, DAO, 2 AS GROUP_TYPE,
	'PAYROLL' AS PRODUCT, NO_NEW_PAYROLL AS QUANTITY, 0 AS BALANCE
 FROM PAYROLL_PERFORMANCE_LUY_KE WHERE NO_NEW_PAYROLL > 0

INSERT CASH_MANAGEMENT_PERFORMANCE_RAW
SELECT YEARMONTH, DAO, 1 AS GROUP_TYPE,
	'I2B' AS PRODUCT, COUNT_CIF AS QUANTITY, TRANS_AMOUNT AS BALANCE
 FROM B2B_PERFROMANCE
 
INSERT CASH_MANAGEMENT_PERFORMANCE_RAW
SELECT YEARMONTH, DAO, 2 AS GROUP_TYPE,
	'I2B' AS PRODUCT, NO_NEW_USER AS QUANTITY, 0 AS BALANCE
 FROM B2B_PERFROMANCE WHERE NO_NEW_USER >0



 SELECT * FROM CASH_MANAGEMENT_PERFORMANCE_RAW

 DELETE FROM CASH_MANAGEMENT_PERFORMANCE_RAW WHERE DAO IS NULL OR DAO = ''

 delete from TBL_SP_CASH_MANAGEMENT_EOP

DECLARE @MONTH0 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -1, GETDATE())),112)
 DECLARE @MONTH_1 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -2, GETDATE())),112)
DECLARE @MONTH2 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -3, GETDATE())),112)
DECLARE @MONTH3 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -4, GETDATE())),112)
DECLARE @MONTH4 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -5, GETDATE())),112)
DECLARE @MONTH5 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -6, GETDATE())),112)
DECLARE @MONTH6 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -7, GETDATE())),112)
DECLARE @MONTH7 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -8, GETDATE())),112)
DECLARE @MONTH8 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -9, GETDATE())),112)
DECLARE @MONTH9 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -10, GETDATE())),112)
DECLARE @MONTH10 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -11, GETDATE())),112)
DECLARE @MONTH11 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -12, GETDATE())),112)
 
EXEC('
Insert TBL_SP_CASH_MANAGEMENT_EOP
SELECT dao, product, group_type, col, ['+@MONTH0+'] as [month0],['+@MONTH_1+'] as [month1],['+@MONTH2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11]
FROM
(SELECT YEARMONTH, DAO,PRODUCT,GROUP_TYPE, COL, VALUE FROM CASH_MANAGEMENT_PERFORMANCE_RAW
CROSS APPLY
( SELECT ''QUANTITY'', CAST(QUANTITY AS NUMERIC) UNION ALL
  SELECT ''BALANCE'', CAST(BALANCE AS NUMERIC) ) C(COL, VALUE)) D
PIVOT
( MAX(VALUE)
  FOR YEARMONTH IN (['+@MONTH0+'],['+@MONTH_1+'],['+@MONTH2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'])) PIV;
')

--declare @count int
  set @count = 0

  while (@count <12)
  begin 
  exec('
  update TBL_SP_CASH_MANAGEMENT_EOP set month'+@count+'=0 where MONTH'+@count+' is null

  update TBL_SP_CASH_MANAGEMENT_EOP set month'+@count+'=0 where col=''balance'' and group_type=2
  ')
  set @count = @count+1
  end

  
 delete from  [SERVER12].[SMECustomer360].dbo.TBL_SP_CASH_MANAGEMENT_EOP

insert [SERVER12].[SMECustomer360].dbo.TBL_SP_CASH_MANAGEMENT_EOP
select * from TBL_SP_CASH_MANAGEMENT_EOP

  --select * from TBL_SP_CASH_MANAGEMENT_EOP order by dao